package GestionDeDatos;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import ParserHorarios.*;

public class AdministradorBD {
	
	protected Connection connection = null;
	protected Statement statement = null;
	
	/******************************************************************/
	/** 					CONEXION Y DESCONEXION					 **/
	/******************************************************************/
	
	public void Conectar(String path, String database, String user, String password) throws Exception {
		
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		this.connection = DriverManager.getConnection("jdbc:mysql://" + path + "/" + database, user, password);
		this.statement = connection.createStatement();
	}
	
	public void Desconectar() throws Exception {
		
		if (this.statement != null) {
			try {
				this.statement.close();
			} catch (SQLException SQLE) {
				SQLE.printStackTrace();
			}
		}
		if (this.connection != null) {
			try {
				this.connection.close();
			} catch (SQLException SQLE) {
				SQLE.printStackTrace();
			}
		}
	}
	
	/******************************************************************/
	/** 					CREACION DE TABLAS						 **/
	/******************************************************************/
	
	public void CrearTablasUsuarios() throws SQLException {
		
		String SQLQuery = "CREATE TABLE usuarios (padron int PRIMARY KEY, nombre varchar(30), apellido varchar(30), " +
				"mail varchar(40), contraseña varchar(35)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
		SQLQuery = "CREATE TABLE solicitudes (padronPide int, padronAcepta int, " +
				"FOREIGN KEY (padronPide) REFERENCES usuarios(padron) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"PRIMARY KEY (padronPide, padronAcepta)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
		SQLQuery = "CREATE TABLE amistades (padron1 int, padron2 int, " +
				"FOREIGN KEY (padron1) REFERENCES usuarios(padron) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (padron2) REFERENCES usuarios(padron) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"PRIMARY KEY (padron1, padron2)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
	}
	
	public void CrearTablasInfoFacultad() throws SQLException {
		
		String SQLQuery = "CREATE TABLE departamentos (codigo int PRIMARY KEY, nombre varchar(50)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
		SQLQuery = "CREATE TABLE materias (departamento int, codigo int, nombre varchar(50), " +
				"FOREIGN KEY (departamento) REFERENCES departamentos(codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"PRIMARY KEY (departamento, codigo)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
		SQLQuery = "CREATE TABLE cursos (departamento int, materia int, " +
				"codigo varchar(5), catedra varchar(50), " +
				"FOREIGN KEY (departamento) REFERENCES departamentos(codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento, materia) REFERENCES materias(departamento, codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"PRIMARY KEY (departamento, materia, codigo)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
		
		SQLQuery = "CREATE TABLE horarios (departamento int, materia int, curso varchar(5), " +
				"tipo varchar(3), dia varchar(10), inicio time, fin time, sede varchar(2), aula varchar(7), " +
				"FOREIGN KEY (departamento) REFERENCES departamentos(codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento, materia) REFERENCES materias(departamento, codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento, materia, curso) REFERENCES cursos(departamento, materia, codigo) ON UPDATE CASCADE ON DELETE CASCADE) " +
				"ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
	}
	
	public void CrearTablaInscripciones() throws SQLException {
		
		String SQLQuery = "CREATE TABLE inscripciones (padron int, departamento int, materia int, curso varchar(5), " +
				"FOREIGN KEY (padron) REFERENCES usuarios(padron) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento) REFERENCES departamentos(codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento, materia) REFERENCES materias(departamento, codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"FOREIGN KEY (departamento, materia, curso) REFERENCES cursos(departamento, materia, codigo) ON UPDATE CASCADE ON DELETE CASCADE, " +
				"PRIMARY KEY (padron, departamento, materia, curso)) ENGINE='InnoDB';";
		statement.executeUpdate(SQLQuery);
	}
	
	public void CrearTablas() throws SQLException {
		
		CrearTablasUsuarios();
		CrearTablasInfoFacultad();
		CrearTablaInscripciones();		
	}
	
	/******************************************************************/
	/** 					ELIMINACION DE TABLAS					 **/
	/******************************************************************/
	
	public void EliminarTablaInscripciones() throws SQLException {
		
		String SQLQuery = "DROP TABLES inscripciones";
		statement.executeUpdate(SQLQuery);
	}
	
	public void EliminarTablasUsuarios() throws SQLException {
		
		String SQLQuery = "DROP TABLES solicitudes, amistades, usuarios";
		statement.executeUpdate(SQLQuery);
	}
	
	public void EliminarTablasInfoFacultad() throws SQLException {
		
		String SQLQuery = "DROP TABLES horarios, cursos, materias, departamentos";
		statement.executeUpdate(SQLQuery);
	}
	
	public void EliminarTablas() throws SQLException {
		
		EliminarTablaInscripciones();
		EliminarTablasUsuarios();
		EliminarTablasInfoFacultad();
	}
	
	/******************************************************************/
	/** 					CARGA DE DATOS FACULTAD					 **/
	/******************************************************************/
	
	public void CargarInfoFacultad(InfoFacultad facultad) {
		
		for (Departamento departamento : facultad.getDepartamentos()) {
			
			CargarRegistroDepartamento(departamento.getCodigo(), departamento.getNombre());
			for (Materia materia : departamento.getMaterias()) {
				
				CargarRegistroMateria(departamento.getCodigo(), materia.getCodigo(), materia.getNombre());
				for (Curso curso : materia.getCursos()) {
					
					CargarRegistroCurso(departamento.getCodigo(), materia.getCodigo(), 
							curso.getCodigo(), curso.getCatedra());
					
					for (Horario horario : curso.getHorarios()) {
						
						CargarRegistroHorario(departamento.getCodigo(), materia.getCodigo(), curso.getCodigo(), 
								horario.getTipo(), horario.getDia(), horario.getInicio(), 
								horario.getFin(), horario.getSede(), horario.getAula());
					}
				}
			}
		}
		
	}
	
	private void CargarRegistroDepartamento(int codigo, String nombre) {
		
		String SQLQuery = "INSERT INTO departamentos VALUES(" + codigo + ", '" + nombre + "');";
		try {
			statement.executeUpdate(SQLQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void CargarRegistroMateria(int departamento, int codigo, String nombre) {
		
		String SQLQuery = "INSERT INTO materias VALUES(" + departamento + ", " + codigo + 
				", '" + nombre + "');";
		try {
			statement.executeUpdate(SQLQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void CargarRegistroCurso(int departamento, int materia, String codigo, String catedra) {
		
		String SQLQuery = "INSERT INTO cursos VALUES(" + departamento + ", " + materia + 
				", '" + codigo + "', '" + catedra + "');";
		try {
			statement.executeUpdate(SQLQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	private void CargarRegistroHorario(int departamento, int materia, String curso,
			String tipo, String dia, String inicio, String fin, String sede, String aula) {
	
		String SQLQuery = "INSERT INTO horarios VALUES(" + departamento + ", " + materia + 
				", '" + curso + "', '" + tipo + "', '" + dia + "', '" + inicio + "', '" + 
				fin + "', '" + sede + "', '" + aula + "');";
		try {
			statement.executeUpdate(SQLQuery);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}